package org.dao;


import org.bean.Staffs;
import org.util.DbUtil;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yang
 * @date 2024-03-07
 */
public class StaffsDao {
    /**
     *
     */
    DbUtil dbUtil = new DbUtil();

    /**
     * 添加用户的方法
     * @param st 用户实体类对象
     * @return 受响应行数
     * @throws SQLException
     */
    public int insertStaffs(Staffs st) throws SQLException {
        String sql = "insert into staffs values(default,?,?,?,?,default,default)";
        Object[] params = {
                st.getName(),
                st.getAge(),
                st.getPhone(),
                st.getPos()
        };

        int rows = dbUtil.executeUpdate(sql,params);
        return rows;

    }

    /**
     * 查询所有用户的方法
     * @return 用户集合
     * @throws SQLException
     */
    public List<Staffs> getAllStaffs() throws SQLException {
        List<Staffs>list = new ArrayList<>();
        Staffs st = null;
        String sql = "select * from staffs";
        ResultSet rs = dbUtil.executeQuery(sql);
        while(rs.next()){
            st = new Staffs();
            st.setId(rs.getInt(1));
            st.setName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setPhone(rs.getString(4));
            st.setPos(rs.getString(5));
            st.setAddTime(rs.getTimestamp(6).toLocalDateTime());
            st.setUpdateTime(rs.getTimestamp(7).toLocalDateTime());
            list.add(st);
        }
        return list;
    }

    /**
     * 根据编号查询用户的方法
     * @param id 用户编号
     * @return 实体类用户对象
     * @throws SQLException
     */
    public Staffs getStaffsbyid(int id) throws SQLException {
        Staffs st = null;
        String sql = "select * from staffs where id = ?";
        ResultSet rs = dbUtil.executeQuery(sql,id);
        if(rs.next()){
            st = new Staffs();
            st.setId(rs.getInt(1));
            st.setName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setPhone(rs.getString(4));
            st.setPos(rs.getString(5));
            st.setAddTime(rs.getTimestamp(6).toLocalDateTime());
            st.setUpdateTime(rs.getTimestamp(7).toLocalDateTime());
        }
        return st;
    }

    /**
     * 分页查询
     * @param limit 当前页码
     * @param count 查询的条数
     * @return 查询结果
     * @throws SQLException
     */
    public List<Staffs> getLimitstaffs(int limit, int count) throws SQLException {
        List<Staffs>list = new ArrayList<>();
        Staffs st = null;
        String sql = "select * from staffs limit ?,?";
        ResultSet rs = dbUtil.executeQuery(sql,limit,count);
        while(rs.next()){
            st = new Staffs();
            st.setId(rs.getInt(1));
            st.setName(rs.getString(2));
            st.setAge(rs.getInt(3));
            st.setPhone(rs.getString(4));
            st.setPos(rs.getString(5));
            st.setAddTime(rs.getTimestamp(6).toLocalDateTime());
            st.setUpdateTime(rs.getTimestamp(7).toLocalDateTime());
            list.add(st);
        }
        return list;
    }
}
